package pt.ua.helpdesk.hddb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import com.jscape.inet.util.MD5;

/**
 * Class responsible for database initialization
 * 
 * @author Amigos do Noddy
 */
public class Database {
	
	/**
	 * Creates the database tables
	 * @throws Exception
	 */
	public void create() throws Exception {
		// establish connection
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
        Connection con = DriverManager.getConnection(
                "jdbc:derby:/home/hp/workspace/helpdesk/hddb;create=true");
        con.setAutoCommit(false);
        Statement s = con.createStatement();
        
        // drop tables if they exist and create
        // new ones. checkout the constraints too
        s.execute("ALTER TABLE Utilizador "+
		"DROP CONSTRAINT FK_Utilizador_Person");
        
        s.execute("ALTER TABLE Assistant "+
		"DROP CONSTRAINT FK_Assistant_Person");
        
        s.execute("ALTER TABLE Administrator " +
		"DROP CONSTRAINT FK_Administrator_Person");
        
        s.execute("ALTER TABLE Ticket "+
		"DROP CONSTRAINT FK_Ticket_Utilizador");
        
        s.execute("ALTER TABLE Comment "+
		"DROP CONSTRAINT FK_Comment_Ticket");
        
        s.execute("ALTER TABLE Comment "+
        "DROP CONSTRAINT FK_Comment_Person");
        
        s.execute("ALTER TABLE Faq " +
		"DROP CONSTRAINT FK_Faq_Assistant");
        
        s.execute("ALTER TABLE Administrator_Faq "+
		"DROP CONSTRAINT FK_AdmFaq_Adm");
        
        s.execute("ALTER TABLE Administrator_Faq "+
		"DROP CONSTRAINT FK_AdmFaq_Faq");
        
        s.execute("ALTER TABLE Assignment " +
		"DROP CONSTRAINT FK_Assigment_Adm");
        
        s.execute("ALTER TABLE Assignment " +
		"DROP CONSTRAINT FK_Assigment_Ass");
        
        s.execute("ALTER TABLE Assignment " +
		"DROP CONSTRAINT FK_Assignment_Ticket");

        s.execute("DROP TABLE Person");
        s.execute("CREATE TABLE Person(" +
                "IDPerson int NOT NULL generated always as identity constraint PK_Person primary key, " +
                "PersonType varchar(100) NOT NULL, " +
                "Name varchar(100) NOT NULL, " +
                "Username varchar(20) NOT NULL, "+
                "Password varchar(100) NOT NULL, "+
                "Email varchar(50) NOT NULL, " +
                "Date DATE NOT NULL, "+
				"Active SMALLINT DEFAULT 1 NOT NULL)");

        System.out.println("Table Person Created");
        
        s.execute("DROP TABLE Utilizador");
        
        s.execute("CREATE TABLE Utilizador(" +
		"RefIDPerson int NOT NULL PRIMARY KEY, " +
		"Code int NOT NULL, " +
		"Valid SMALLINT NOT NULL, " +
		"CONSTRAINT FK_Utilizador_Person " +
		"FOREIGN KEY(RefIDPerson) " +
		"REFERENCES Person(IDPerson))");
        
        System.out.println("Table Utilizador Created");

        s.execute("DROP TABLE Assistant");
        
        s.execute("CREATE TABLE Assistant(" +
		"RefIDPerson int NOT NULL PRIMARY KEY," +
		"CONSTRAINT FK_Assistant_Person " +
		"FOREIGN KEY(RefIDPerson) " +
		"REFERENCES Person(IDPerson))");
        
        System.out.println("Table Assistant Created");
	
        s.execute("DROP TABLE Administrator");
        
        s.execute("CREATE TABLE Administrator(" +
		"RefIDPerson int NOT NULL PRIMARY KEY," +
		"Level SMALLINT NOT NULL," +
		"CONSTRAINT FK_Administrator_Person " +
		"FOREIGN KEY(RefIDPerson) " +
		"REFERENCES Person(IDPerson))");
		
		System.out.println("Table Administrator Created");

		s.execute("DROP TABLE Ticket");
        s.execute("CREATE TABLE Ticket(" +
		"IDTicket int NOT NULL generated always as identity constraint PK_Ticket primary key, " +
		"Ticket LONG VARCHAR NOT NULL, " +
		"Title VARCHAR(100) NOT NULL, " +
		"Date DATE NOT NULL," +
		"Status SMALLINT NOT NULL," +
		"Active SMALLINT DEFAULT 1 NOT NULL," +
		"RefIDPerson_Utilizador int NOT NULL, " +
		"CONSTRAINT FK_Ticket_Utilizador " +
		"FOREIGN KEY(RefIDPerson_Utilizador) " +
		"REFERENCES Utilizador(RefIDPerson))");

        System.out.println("Table Ticket Created");
        
        s.execute("DROP TABLE Comment");
        s.execute("CREATE TABLE Comment(" +
		"IDComment int NOT NULL generated always as identity constraint PK_Comment primary key, " +
		"Comment LONG VARCHAR NOT NULL," +
		"Date DATE NOT NULL," +
		"RefIDPerson_Author int NOT NULL," +
		"RefIDTicket int NOT NULL," +
		"CONSTRAINT FK_Comment_Ticket " +
		"FOREIGN KEY(RefIDTicket) " +
		"REFERENCES Ticket(IDTicket), " +
		"CONSTRAINT FK_Comment_Person " +
		"FOREIGN KEY(RefIDPerson_Author) " +
		"REFERENCES Person(IDPerson))");

        System.out.println("Table Comment Created");
        
        s.execute("DROP TABLE Faq");
        
        s.execute("CREATE TABLE Faq(" +
		"IDFaq int NOT NULL generated always as identity constraint PK_Faq primary key, " +
		"Faq LONG VARCHAR NOT NULL," +
		"Title VARCHAR(100) NOT NULL," +
		"Date DATE NOT NULL," +
		"Accepted SMALLINT NOT NULL," +
		"Active SMALLINT DEFAULT 1 NOT NULL," +
		"RefIDPerson_Assistant int NOT NULL, " +
		"CONSTRAINT FK_Faq_Assistant " +
		"FOREIGN KEY(RefIDPerson_Assistant) " +
		"REFERENCES Assistant(RefIDPerson))");
        
        System.out.println("Table faq Created");

        s.execute("DROP TABLE Administrator_Faq");
        
        s.execute("CREATE TABLE Administrator_Faq(" +
		"RefIDPerson_Adm int NOT NULL," +
		"RefIDFaq int NOT NULL," +
		"PRIMARY KEY (RefIDPerson_Adm, RefIDFaq), " +
		"CONSTRAINT FK_AdmFaq_Adm " +
		"FOREIGN KEY(RefIDPerson_Adm) " +
		"REFERENCES Administrator(RefIDPerson), " +
		"CONSTRAINT FK_AdmFaq_Faq " +
		"FOREIGN KEY(RefIDFaq) " +
		"REFERENCES Faq(IDFaq))");
        
        System.out.println("Table Admin_Faq Created");

        s.execute("DROP TABLE Assignment");
        
        s.execute("CREATE TABLE Assignment(" +
		"RefIDPerson_Adm int NOT NULL, " +
		"RefIDPerson_Ass int NOT NULL, " +
		"RefIDTicket int NOT NULL, " +
		"PRIMARY KEY (RefIDPerson_Adm, RefIDPerson_Ass, RefIDTicket), " +
		"CONSTRAINT FK_Assigment_Adm " +
		"FOREIGN KEY(RefIDPerson_Adm) " +
		"REFERENCES Administrator(RefIDPerson), " +
		"CONSTRAINT FK_Assigment_Ass " +
		"FOREIGN KEY(RefIDPerson_Ass) " +
		"REFERENCES Assistant(RefIDPerson), " +
		"CONSTRAINT FK_Assignment_Ticket " +
		"FOREIGN KEY(RefIDTicket) " +
		"REFERENCES Ticket(IDTicket))");
        
        System.out.println("Table assignment Created");

        // initialize data (for testing purposes)
        MD5 md5 = new MD5("user");
        s.execute("insert into Person(Name, PersonType, Username, Password, Email, Date) Values('user', 'Utilizador', 'user', '"+md5.getHash()+"', 'user@somewhere.pt', '1993-09-01')");
        s.execute("insert into Utilizador(RefIDPerson, Code, Valid) Values(IDENTITY_VAL_LOCAL(),0, 1)");
        
        MD5 md52 = new MD5("ass");
        s.execute("insert into Person(Name, PersonType, Username, Password, Email, Date) Values('ass', 'Assistente', 'ass', '"+md52.getHash()+"', 'ass@aauav-helpdesk.pt', '1993-09-01')");
        s.execute("insert into Assistant(RefIDPerson) Values(IDENTITY_VAL_LOCAL())");
        
        MD5 md53 = new MD5("adm");
        s.execute("insert into Person(Name, PersonType, Username, Password, Email, Date) Values('adm', 'Administrador', 'adm', '"+md53.getHash()+"', 'adm@aauav-helpdesk.pt', '1993-09-01')");
        s.execute("insert into Administrator(RefIDPerson, Level) Values(IDENTITY_VAL_LOCAL(), 1)");
        
        MD5 md54 = new MD5("ceo");
        s.execute("insert into Person(Name, PersonType, Username, Password, Email, Date) Values('ceo', 'Administrador', 'ceo', '"+md54.getHash()+"', 'ceo@aauav-helpdesk.pt', '1993-09-01')");
        s.execute("insert into Administrator(RefIDPerson, Level) Values(IDENTITY_VAL_LOCAL(), 2)");
        
        /*     
        s.execute("INSERT INTO Ticket(Ticket, Title, Date, Status, RefIDPerson_Utilizador) VALUES('asd', 'asd', CURRENT_DATE, 1, 1)");
        s.execute("INSERT INTO Ticket(Ticket, Title, Date, Status, RefIDPerson_Utilizador) VALUES('asd', 'asd', CURRENT_DATE, 1, 1)");
        s.execute("INSERT INTO Ticket(Ticket, Title, Date, Status, RefIDPerson_Utilizador) VALUES('asd', 'asd', CURRENT_DATE, 1, 1)");
        s.execute("INSERT INTO Ticket(Ticket, Title, Date, Status, RefIDPerson_Utilizador) VALUES('asd', 'asd', CURRENT_DATE, 1, 1)");
        s.execute("INSERT INTO Ticket(Ticket, Title, Date, Status, RefIDPerson_Utilizador) VALUES('asd', 'asd', CURRENT_DATE, 1, 1)");
        s.execute("INSERT INTO Ticket(Ticket, Title, Date, Status, RefIDPerson_Utilizador) VALUES('asd', 'asd', CURRENT_DATE, 1, 1)");
        */
        
        System.out.println("Dados Base Criados");
        //
        // All the other create table statements from above would go here...
        //

        con.commit() ;
        con.close() ;

        try {
            DriverManager.getConnection("jdbc:derby:;shutdown=true");
        } catch (SQLException ignore) {}
    }    
    
	/**
	 * Main process. Runs the create table method
	 * 
	 * @param args Command line arguments
	 * @throws Exception
	 */
    public static void main(String[] args) throws Exception {
        (new Database()).create();
    }
}
